﻿/*
    Copyright (c) 2012, Matías López
    All rights reserved.

    Redistribution and use in source and binary forms, with or without modification, are permitted 
    provided that the following conditions are met:
        *   Redistributions of source code must retain the above copyright notice, this list of 
            conditions and the following disclaimer.
        *   Redistributions in binary form must reproduce the above copyright notice, this list of 
            conditions and the following disclaimer in the documentation and/or other materials 
            provided with the distribution.
        *   Neither the name of Matías López nor the names of its contributors may be used to 
            endorse or promote products derived from this software without specific prior written 
            permission.
    
    THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR 
    IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY 
    AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR 
    CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
    CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR 
    SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 
    THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR 
    OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
    POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace mana.Data.Excel
{
    public class Transaccion : IDisposable
    {
        private bool blnEsExcel2007;
        private OleDbConnection objConexion;
        private OleDbCommand objComando;
        private OleDbDataReader objDataReader;
        private OleDbDataAdapter objDataAdapter;

        public Transaccion(string nombreArchivo)
        {
            blnEsExcel2007 = (System.IO.Path.GetExtension(nombreArchivo) == ".xlsx");

            /*
             *  PRB: Excel Values Returned as NULL Using DAO OpenRecordset
             *  http://support.microsoft.com/kb/194124
             */

            objConexion = new OleDbConnection();
            
            objConexion.ConnectionString = CrearConnectionString(nombreArchivo);
            objConexion.Open();

            objComando = objConexion.CreateCommand();
            objComando.CommandType = CommandType.Text;

            objDataAdapter = new OleDbDataAdapter(objComando);
        }

        public List<string> ObtenerNombresDeTablas()
        {
            DataTable tablasSchema = objConexion.GetSchema("Tables");
            
            List<string> nombres = new List<string>();
            foreach(DataRow tablaSchema in tablasSchema.Rows)
            {
                string nombre = Convert.ToString(tablaSchema["TABLE_NAME"]).TrimEnd(new char[2] { '$', '\'' });
                nombres.Add(nombre);
            }

            return nombres;
        }

        public void CrearTabla(string nombreTabla, NameValueCollection campos)
        {
            ValidarNombreTabla(nombreTabla);
            ValidarDefinicionCampos(campos);

            StringBuilder DefinicionCampos = new StringBuilder();
            DefinicionCampos.Append(campos.Keys[0] + " " + campos[0]);
            int Cant = campos.Count;
            for (int i = 1; i < Cant; i++)
                DefinicionCampos.Append(", " + campos.Keys[i] + " " + campos[i]);

            ExecuteNonQuery("CREATE TABLE " + nombreTabla + "(" + DefinicionCampos.ToString() + ")");
        }

        public DataTable ObtenerDataTable(string nombreTabla)
        {
            return ObtenerDataTable(nombreTabla, string.Empty, string.Empty);
        }

        public DataTable ObtenerDataTable(string nombreTabla, string condiciones)
        {
            return ObtenerDataTable(nombreTabla, condiciones, string.Empty);
        }

        public DataTable ObtenerDataTable(string nombreTabla, string condiciones, string orden)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM [" + nombreTabla + "$]");

            if (!string.IsNullOrEmpty(condiciones)) 
                sb.Append(" WHERE " + condiciones);

            if (!string.IsNullOrEmpty(orden))
                sb.Append(" ORDER BY " + orden);

            objComando.CommandText = sb.ToString();
            
            DataTable ret = new DataTable();
            objDataAdapter.Fill(ret);

            return ret;
        }

        public int Insert(string nombreTabla, SortedList<string, object> datos)
        {
            ValidarNombreTabla(nombreTabla);
            ValidarDatos(datos);

            StringBuilder Nombres = new StringBuilder();
            StringBuilder Valores = new StringBuilder();
            
            Nombres.Append(datos.Keys[0]);
            int Cant = datos.Count;
            int i;
            for (i = 1; i < Cant; i++)
                Nombres.Append(", " + datos.Keys[i]);

            Valores.Append(Formato.Formatear(datos.Values[0]));
            for (i = 1; i < Cant; i++)
                Valores.Append(", " + Formato.Formatear(datos.Values[i]));

            return ExecuteNonQuery("INSERT INTO [" + nombreTabla + "$] (" + Nombres.ToString() + ") VALUES (" + Valores.ToString() + ")");
        }

        public int Update(string nombreTabla, SortedList<string, object> datos, string condiciones)
        {
            ValidarNombreTabla(nombreTabla);
            ValidarDatos(datos);

            StringBuilder NombresValores = new StringBuilder();
            
            NombresValores.Append(datos.Keys[0] + " = " + Formato.Formatear(datos.Values[0]));
            int Cant = datos.Count;
            int i;
            for (i = 1; i < Cant; i++)
                NombresValores.Append(", " + datos.Keys[i] + " = " + Formato.Formatear(datos.Values[i]));

            return ExecuteNonQuery("UPDATE [" + nombreTabla + "$] SET " + NombresValores.ToString() + " WHERE " + condiciones);
        }

        public Lector Select(string nombreTabla, List<string> campos)
        {
            return Select(nombreTabla, campos, string.Empty, string.Empty);
        }

        public Lector Select(string nombreTabla, List<string> campos, string condiciones)
        {
            return Select(nombreTabla, campos, condiciones, string.Empty);
        }

        public Lector Select(string nombreTabla, List<string> campos, string condiciones, string orden)
        {
            StringBuilder NombresCampos = new StringBuilder();
            NombresCampos.Append(campos[0]);
            int Cant = campos.Count;
            for (int i = 1; i < Cant; i++)
                NombresCampos.Append(", " + campos[i]);

            return Select(nombreTabla, NombresCampos.ToString(), condiciones, orden);
        }

        public Lector SelectAsterisco(string nombreTabla)
        {
            return SelectAsterisco(nombreTabla, string.Empty, string.Empty);
        }

        public Lector SelectAsterisco(string nombreTabla, string condiciones)
        {
            return SelectAsterisco(nombreTabla, condiciones, string.Empty);
        }

        public Lector SelectAsterisco(string nombreTabla, string condiciones, string orden)
        {
            return Select(nombreTabla, "*", condiciones, orden);
        }

        private int ExecuteNonQuery(string query)
        {
            objComando.CommandText = query;
            return objComando.ExecuteNonQuery();
        }

        private Lector Select(string nombreTabla, string campos, string condiciones, string orden)
        {
            ValidarNombreTabla(nombreTabla);
            ValidarNombreCampos(campos);

            StringBuilder Instruccion = new StringBuilder();
            Instruccion.Append("SELECT " + campos + " FROM [" + nombreTabla + "$]");

            if (!string.IsNullOrEmpty(condiciones))
                Instruccion.Append(" WHERE " + condiciones);

            if (!string.IsNullOrEmpty(orden))
                Instruccion.Append(" ORDER BY " + condiciones);

            return ExecuteReader(Instruccion.ToString());
        }

        private Lector ExecuteReader(string query)
        {
            objComando.CommandText = query;
            objDataReader = objComando.ExecuteReader();
            Lector Lector = new Lector(objDataReader);
            return Lector;
        }

        public void Close()
        {
            if (objDataReader != null)
            {
                objComando.Cancel();
                objDataReader.Close();
            }

            objConexion.Close();
        }

        public void Dispose()
        {
            Close();

            objComando.Dispose();
            objConexion.Dispose();
        }

        private string CrearConnectionString(string nombreArchivo)
        {
            string Provider;
            string ExtendedProperties;

            if (blnEsExcel2007)
            {
                Provider = "Microsoft.ACE.OLEDB.12.0";
                ExtendedProperties = "Excel 12.0 Xml;HDR=YES";
            }
            else
            {
                Provider = "Microsoft.Jet.OLEDB.4.0";
                ExtendedProperties = "Excel 8.0;HDR=Yes";
            }
            
            return "Provider=" + Provider + ";Data Source=" + nombreArchivo + ";Extended Properties=\"" + ExtendedProperties + "\"";
        }

        private void ValidarNombreTabla(string valor)
        {
            if (string.IsNullOrEmpty(valor))
                throw new Exception("Debe especificar el nombre de la tabla.");
        }

        private void ValidarDatos(SortedList<string, object> datos)
        {
            if (datos == null || datos.Count == 0)
                throw new Exception("Debe especificar los datos a insertar.");
        }

        private void ValidarDefinicionCampos(NameValueCollection campos)
        {
            if (campos == null || campos.Count == 0)
                throw new Exception("Debe especificar los campos de la tabla.");
        }

        private void ValidarCampos(List<string> campos)
        {
            if (campos == null || campos.Count == 0)
                throw new Exception("Debe especificar los campos a seleccionar.");
        }

        private void ValidarNombreCampos(string campos)
        {
            if (string.IsNullOrEmpty(campos))
                throw new Exception("Debe especificar los campos a seleccionar.");
        }

    }
}
